<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js" lang="en" > <!--<![endif]-->
<head>
  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  
  <meta name="author" content="PG-Strom Development Team">
  <link rel="shortcut icon" href="../img/favicon.ico">
  <title>SQL Objects - PG-Strom Manual</title>
  <link href='https://fonts.googleapis.com/css?family=Lato:400,700|Roboto+Slab:400,700|Inconsolata:400,700' rel='stylesheet' type='text/css'>

  <link rel="stylesheet" href="../css/theme.css" type="text/css" />
  <link rel="stylesheet" href="../css/theme_extra.css" type="text/css" />
  <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/github.min.css">
  <link href="//fonts.googleapis.com/earlyaccess/notosansjp.css" rel="stylesheet">
  <link href="//fonts.googleapis.com/css?family=Open+Sans:600,800" rel="stylesheet">
  <link href="../custom.css" rel="stylesheet">
  
  <script>
    // Current page data
    var mkdocs_page_name = "SQL Objects";
    var mkdocs_page_input_path = "ref_sqlfuncs.md";
    var mkdocs_page_url = null;
  </script>
  
  <script src="../js/jquery-2.1.1.min.js" defer></script>
  <script src="../js/modernizr-2.8.3.min.js" defer></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js"></script>
  <script>hljs.initHighlightingOnLoad();</script> 
  
</head>

<body class="wy-body-for-nav" role="document">

  <div class="wy-grid-for-nav">

    
    <nav data-toggle="wy-nav-shift" class="wy-nav-side stickynav">
      <div class="wy-side-nav-search">
        <a href=".." class="icon icon-home"> PG-Strom Manual</a>
        <div role="search">
  <form id ="rtd-search-form" class="wy-form" action="../search.html" method="get">
    <input type="text" name="q" placeholder="Search docs" />
  </form>
  [<a href="../ja/ref_sqlfuncs/" style="color: #cccccc">Japanese</a> | <strong>English</strong>]
</div>
      </div>

      <div class="wy-menu wy-menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
	<ul class="current">
	  
          
            <li class="toctree-l1">
		
    <a class="" href="..">Home</a>
	    </li>
          
            <li class="toctree-l1">
		
    <a class="" href="../install/">Install</a>
	    </li>
          
            <li class="toctree-l1">
		
    <span class="caption-text">Tutorial</span>
    <ul class="subnav">
                <li class="">
                    
    <a class="" href="../operations/">Basic Operations</a>
                </li>
                <li class="">
                    
    <a class="" href="../sys_admin/">System Administration</a>
                </li>
                <li class="">
                    
    <a class="" href="../brin/">Index Support</a>
                </li>
                <li class="">
                    
    <a class="" href="../partition/">Partitioning</a>
                </li>
                <li class="">
                    
    <a class="" href="../troubles/">Trouble Shooting</a>
                </li>
    </ul>
	    </li>
          
            <li class="toctree-l1">
		
    <span class="caption-text">Advanced Features</span>
    <ul class="subnav">
                <li class="">
                    
    <a class="" href="../ssd2gpu/">SSD2GPU Direct SQL</a>
                </li>
                <li class="">
                    
    <a class="" href="../arrow_fdw/">Arrow_fdw</a>
                </li>
                <li class="">
                    
    <a class="" href="../gstore_fdw/">Gstore_fdw</a>
                </li>
                <li class="">
                    
    <a class="" href="../plcuda/">PL/CUDA</a>
                </li>
    </ul>
	    </li>
          
            <li class="toctree-l1">
		
    <span class="caption-text">References</span>
    <ul class="subnav">
                <li class="">
                    
    <a class="" href="../ref_types/">Data Types</a>
                </li>
                <li class="">
                    
    <a class="" href="../ref_devfuncs/">Functions and Operators</a>
                </li>
                <li class=" current">
                    
    <a class="current" href="./">SQL Objects</a>
    <ul class="subnav">
            
    <li class="toctree-l3"><a href="#device-information">Device Information</a></li>
    

    <li class="toctree-l3"><a href="#array-based-matrix-support">Array-based matrix support</a></li>
    
        <ul>
        
            <li><a class="toctree-l4" href="#type-cast">Type cast</a></li>
        
            <li><a class="toctree-l4" href="#array-based-matrix-functions">Array-based matrix functions</a></li>
        
            <li><a class="toctree-l4" href="#aggregate-functions">Aggregate functions</a></li>
        
        </ul>
    

    <li class="toctree-l3"><a href="#miscellaneous-functions">Miscellaneous functions</a></li>
    

    <li class="toctree-l3"><a href="#system-view">System View</a></li>
    

    </ul>
                </li>
                <li class="">
                    
    <a class="" href="../ref_params/">GUC Parameters</a>
                </li>
    </ul>
	    </li>
          
            <li class="toctree-l1">
		
    <a class="" href="../release_note/">Release Note</a>
	    </li>
          
        </ul>
      </div>
      &nbsp;
    </nav>

    <section data-toggle="wy-nav-shift" class="wy-nav-content-wrap">

      
      <nav class="wy-nav-top" role="navigation" aria-label="top navigation">
        <i data-toggle="wy-nav-top" class="fa fa-bars"></i>
        <a href="..">PG-Strom Manual</a>
      </nav>

      
      <div class="wy-nav-content">
        <div class="rst-content">
          <div role="navigation" aria-label="breadcrumbs navigation">
  <ul class="wy-breadcrumbs">
    <li><a href="..">Docs</a> &raquo;</li>
    
      
        
          <li>References &raquo;</li>
        
      
    
    <li>SQL Objects</li>
    <li class="wy-breadcrumbs-aside">
      
    </li>
  </ul>
  <hr/>
</div>
          <div role="main">
            <div class="section">
              
                <h1>SQL Objects</h1>

<p>This chapter introduces SQL objects additionally provided by PG-Strom.</p>
<h1 id="device-information">Device Information</h1>
<table>
<thead>
<tr>
<th align="left">Function</th>
<th align="center">Result</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>gpu_device_name(int = 0)</code></td>
<td align="center"><code>text</code></td>
<td align="left">It tells name of the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_global_memsize(int = 0)</code></td>
<td align="center"><code>bigint</code></td>
<td align="left">It tells amount of the specified GPU device in bytes.</td>
</tr>
<tr>
<td align="left"><code>gpu_max_blocksize(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells maximum block-size on the specified GPU device. 1024, in the currently supported GPU models.</td>
</tr>
<tr>
<td align="left"><code>gpu_warp_size(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells warp-size on the specified GPU device. 32, in the currently supported GPU models.</td>
</tr>
<tr>
<td align="left"><code>gpu_max_shared_memory_perblock(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells maximum shared memory size per block on the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_num_registers_perblock(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells total number of registers per block on the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_num_multiptocessors(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells number of SM(Streaming Multiprocessor) units on the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_num_cuda_cores(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells number of CUDA cores on the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_cc_major(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells major CC(Compute Capability) version of the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_cc_minor(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells minor CC(Compute Capability) version of the specified GPU device.</td>
</tr>
<tr>
<td align="left"><code>gpu_pci_id(int = 0)</code></td>
<td align="center"><code>int</code></td>
<td align="left">It tells PCI bus-id of the specified GPU device.</td>
</tr>
</tbody>
</table>
<h1 id="array-based-matrix-support">Array-based matrix support</h1>
<p>You can use array data type of PostgreSQL to deliver matrix-data for PL/CUDA functions.
A two-dimensional array of fixed-length boolean/numeric values without NULL has flat data structure (expect for the array header). It allows to identify the address of elements by indexes of the matrix uniquely.
PG-Strom provides several SQL functions to handle array-based matrix.</p>
<h2 id="type-cast">Type cast</h2>
<table>
<thead>
<tr>
<th align="left">destination type</th>
<th align="left">source type</th>
<th align="left">description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>int[]</code></td>
<td align="left"><code>bit</code></td>
<td align="left">convert bit-string to 32bit integer array. Unaligned bits are filled up by 0.</td>
</tr>
<tr>
<td align="left"><code>bit</code></td>
<td align="left"><code>int[]</code></td>
<td align="left">convert 32bit integer to bit-string</td>
</tr>
</tbody>
</table>
<h2 id="array-based-matrix-functions">Array-based matrix functions</h2>
<table>
<thead>
<tr>
<th align="left">functions/operators</th>
<th align="left">result</th>
<th align="left">description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>array_matrix_validation(anyarray)</code></td>
<td align="left"><code>bool</code></td>
<td align="left">It checks whether the supplied array satisfies the requirement of array-based matrix.</td>
</tr>
<tr>
<td align="left"><code>array_matrix_height(anyarray)</code></td>
<td align="left"><code>int</code></td>
<td align="left">It tells height of the array-based matrix.</td>
</tr>
<tr>
<td align="left"><code>array_matrix_width(anyarray)</code></td>
<td align="left"><code>int</code></td>
<td align="left">It tells width of the array-based matrix.</td>
</tr>
<tr>
<td align="left"><code>array_vector_rawsize(regtype,int)</code></td>
<td align="left"><code>bigint</code></td>
<td align="left">It tells expected size if N-items vector is created with the specified type.</td>
</tr>
<tr>
<td align="left"><code>array_matrix_rawsize(regtype,int,int)</code></td>
<td align="left"><code>bigint</code></td>
<td align="left">It tells expected size if HxW matrix is created with the specified type.</td>
</tr>
<tr>
<td align="left"><code>array_cube_rawsize(regtype,int,int,int)</code></td>
<td align="left"><code>bigint</code></td>
<td align="left">It tells expected size if HxWxD cube is created with the specified type.</td>
</tr>
<tr>
<td align="left"><code>type_len(regtype)</code></td>
<td align="left"><code>bigint</code></td>
<td align="left">It tells unit length of the specified type.</td>
</tr>
<tr>
<td align="left"><code>composite_type_rawsize(LEN,...)</code></td>
<td align="left"><code>bigint</code></td>
<td align="left">It tells expected size of the composite type if constructed with the specified data-length order. We expect to use the function with <code>type_len()</code><br><code>LEN</code> is either of <code>int,bigint</code></td>
</tr>
<tr>
<td align="left"><code>matrix_unnest(anyarray)</code></td>
<td align="left"><code>record</code></td>
<td align="left">It is a function to return set, to fetch rows from top of the supplied array-based matrix. PostgreSQL has no type information of the record, so needs to give type information using <code>ROW()</code> clause.</td>
</tr>
<tr>
<td align="left"><code>rbind(MATRIX,MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It combines two array-based matrix vertically.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
<tr>
<td align="left"><code>rbind(TYPE,MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It adds a scalar value on head of the array-based matrix. If multiple columns exist, the scalar value shall be set on all the column of the head row.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code>.<br><code>TYPE</code> is element of <code>MATRIX</code></td>
</tr>
<tr>
<td align="left"><code>rbind(MATRIX,TYPE)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It adds a scalar value on bottom of the array-based matrix. If multiple columns exist, the scalar value shall be set on all the column of the bottom row.<code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code><br><code>TYPE</code> is element type of <code>MATRIX</code></td>
</tr>
<tr>
<td align="left"><code>cbind(MATRIX,MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It combines two array-based matrix horizontally.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
<tr>
<td align="left"><code>cbind(TYPE,MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It adds a scalar value on left of the array-based matrix. If multiple rows exist, the scalar value shall be set on all the rows of the left column.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code><br><code>TYPE</code> is element type of <code>MATRIX</code></td>
</tr>
<tr>
<td align="left"><code>cbind(MATRIX,TYPE)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It adds a scalar value on right of the array-based matrix. If multiple rows exist, the scalar value shall be set on all the rows of the right column.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code>.<br><code>TYPE</code> is element type of <code>MATRIX</code></td>
</tr>
<tr>
<td align="left"><code>transpose(MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">It transposes the array-based matrix.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
</tbody>
</table>
<h2 id="aggregate-functions">Aggregate functions</h2>
<table>
<thead>
<tr>
<th align="left">functions/operators</th>
<th align="left">result</th>
<th align="left">description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>array_matrix(TYPE,...)</code></td>
<td align="left"><code>TYPE[]</code></td>
<td align="left">An aggregate function with varidic arguments. It produces M-cols x N-rows array-based matrix if N-rows were supplied with M-columns.<br><code>TYPE</code> is any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
<tr>
<td align="left"><code>array_matrix(bit)</code></td>
<td align="left"><code>bit[]</code></td>
<td align="left">An aggregate function to produce <code>int4[]</code> array-based matrix. It considers bit-string as a set of 32bits integer values.</td>
</tr>
<tr>
<td align="left"><code>rbind(MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">An aggregate function to combine the supplied array-based matrix vertically.<br><code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
<tr>
<td align="left"><code>cbind(MATRIX)</code></td>
<td align="left"><code>MATRIX</code></td>
<td align="left">An aggregate function to combine the supplied array-based matrix horizontally.<code>MATRIX</code> is array type of any of <code>bool,int2,int4,int8,float4,float8</code></td>
</tr>
</tbody>
</table>
<h1 id="miscellaneous-functions">Miscellaneous functions</h1>
<table>
<thead>
<tr>
<th align="left">Function</th>
<th align="center">Result</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>gstore_fdw_format(reggstore)</code></td>
<td align="center"><code>text</code></td>
<td align="left">It tells internal format of the specified gstore_fdw foreign table.</td>
</tr>
<tr>
<td align="left"><code>gstore_fdw_nitems(reggstore)</code></td>
<td align="center"><code>bigint</code></td>
<td align="left">It tells number of rows of the specified gstore_fdw foreign table.</td>
</tr>
<tr>
<td align="left"><code>gstore_fdw_nattrs(reggstore)</code></td>
<td align="center"><code>bigint</code></td>
<td align="left">It tells number of columns of the specified gstore_fdw foreign table.</td>
</tr>
<tr>
<td align="left"><code>gstore_fdw_rawsize(reggstore)</code></td>
<td align="center"><code>bigint</code></td>
<td align="left">It tells raw size of the specified gstore_fdw foreign table in bytes.</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th align="left">Function</th>
<th align="center">Result</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>gstore_export_ipchandle(reggstore)</code></td>
<td align="center"><code>bytea</code></td>
<td align="left">It tells IPC-handle of the GPU device memory region of the specified gstore_fdw foreign table.</td>
</tr>
<tr>
<td align="left"><code>lo_import_gpu(int, bytea, bigint, bigint, oid=0)</code></td>
<td align="center"><code>oid</code></td>
<td align="left">It maps GPU device memory region acquired by external application, then import its contents into a largeobject.</td>
</tr>
<tr>
<td align="left"><code>lo_export_gpu(oid, int, bytea, bigint, bigint)</code></td>
<td align="center"><code>bigint</code></td>
<td align="left">It maps GPU device memory region acquired by external application, then export contents of the specified largeobject into the region.</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th align="left">Definition</th>
<th align="center">Result</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>attnums_of(regclass,text[])</code></td>
<td align="center"><code>smallint[]</code></td>
<td align="left">It returns attribute numbers for the column names (may be multiple) of the 2nd argument on the table of the 1st argument.</td>
</tr>
<tr>
<td align="left"><code>attnum_of(regclass,text)</code></td>
<td align="center"><code>smallint</code></td>
<td align="left">It returns attribute number for the column name of the 2nd argument on the table of the 1st argument.</td>
</tr>
<tr>
<td align="left"><code>atttypes_of(regclass,text[])</code></td>
<td align="center"><code>regtype[]</code></td>
<td align="left">It returns data types for the column names (may be multiple) of the 2nd argument on the table of the 1st argument.</td>
</tr>
<tr>
<td align="left"><code>atttype_of(regclass,text)</code></td>
<td align="center"><code>regtype</code></td>
<td align="left">It returns data type for the column name of the 2nd argument on the table of the 1st argument.</td>
</tr>
<tr>
<td align="left"><code>attrs_types_check(regclass,text[],regtype[])</code></td>
<td align="center"><code>bool</code></td>
<td align="left">It checks whether the data types of the columns (may be multiple) of the 2nd argument on the table of the 1st argument match with the data types of the 3rd argument for each.</td>
</tr>
<tr>
<td align="left"><code>attrs_type_check(regclass,text[],regtype)</code></td>
<td align="center"><code>bool</code></td>
<td align="left">It checks whether all the data types of the columns (may be multiple) of the 2nd argument on the table of the 1st argument match with the data type of the 3rd argument.</td>
</tr>
</tbody>
</table>
<table>
<thead>
<tr>
<th align="left">Function</th>
<th align="center">Result</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left"><code>pgstrom.license_validation()</code></td>
<td align="center"><code>text</code></td>
<td align="left">It validates commercial subscription.</td>
</tr>
<tr>
<td align="left"><code>pgstrom.license_query()</code></td>
<td align="center"><code>text</code></td>
<td align="left">It shows the active commercial subscription.</td>
</tr>
</tbody>
</table>
<h1 id="system-view">System View</h1>
<p>PG-Strom provides several system view to export its internal state for users or applications.
The future version may add extra fields here. So, it is not recommended to reference these information schemas using <code>SELECT * FROM ...</code>.</p>
<p><strong>pgstrom.device_info</strong></p>
<p><code>pgstrom.device_into</code> system view exports device attributes of the GPUs recognized by PG-Strom.
GPU has different specification for each model, like number of cores, capacity of global memory, maximum number of threads and etc, user's software should be optimized according to the information if you try raw GPU programming with PL/CUDA functions.</p>
<table>
<thead>
<tr>
<th align="left">Name</th>
<th align="left">Data Type</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">device_nr</td>
<td align="left"><code>int</code></td>
<td align="left">GPU device number</td>
</tr>
<tr>
<td align="left">aindex</td>
<td align="left"><code>int</code></td>
<td align="left">Attribute index</td>
</tr>
<tr>
<td align="left">attribute</td>
<td align="left"><code>text</code></td>
<td align="left">Attribute name</td>
</tr>
<tr>
<td align="left">value</td>
<td align="left"><code>text</code></td>
<td align="left">Value of the attribute</td>
</tr>
</tbody>
</table>
<p><strong>pgstrom.device_preserved_meminfo</strong></p>
<p><code>pgstrom.device_preserved_meminfo</code> system view exports information of the preserved device memory; which can be shared multiple PostgreSQL backend.
Right now, only gstore_fdw uses this feature.</p>
<table>
<thead>
<tr>
<th align="left">Name</th>
<th align="left">Data Type</th>
<th align="left">Description</th>
</tr>
</thead>
<tbody>
<tr>
<td align="left">device_nr</td>
<td align="left"><code>int</code></td>
<td align="left">GPU device number</td>
</tr>
<tr>
<td align="left">handle</td>
<td align="left"><code>bytea</code></td>
<td align="left">IPC handle of the preserved device memory</td>
</tr>
<tr>
<td align="left">owner</td>
<td align="left"><code>regrole</code></td>
<td align="left">Owner of the preserved device memory</td>
</tr>
<tr>
<td align="left">length</td>
<td align="left"><code>bigint</code></td>
<td align="left">Length of the preserved device memory in bytes</td>
</tr>
<tr>
<td align="left">ctime</td>
<td align="left"><code>timestamp with time zone</code></td>
<td align="left">Timestamp when the preserved device memory is created</td>
</tr>
</tbody>
</table>
              
            </div>
          </div>
          <footer>
  
    <div class="rst-footer-buttons" role="navigation" aria-label="footer navigation">
      
        <a href="../ref_params/" class="btn btn-neutral float-right" title="GUC Parameters">Next <span class="icon icon-circle-arrow-right"></span></a>
      
      
        <a href="../ref_devfuncs/" class="btn btn-neutral" title="Functions and Operators"><span class="icon icon-circle-arrow-left"></span> Previous</a>
      
    </div>
  

  <hr/>

  <div role="contentinfo">
    <!-- Copyright etc -->
    
  </div>

  Built with <a href="http://www.mkdocs.org">MkDocs</a> using a <a href="https://github.com/snide/sphinx_rtd_theme">theme</a> provided by <a href="https://readthedocs.org">Read the Docs</a>.
</footer>
      
        </div>
      </div>

    </section>

  </div>

  <div class="rst-versions" role="note" style="cursor: pointer">
    <span class="rst-current-version" data-toggle="rst-current-version">
      
      
        <span><a href="../ref_devfuncs/" style="color: #fcfcfc;">&laquo; Previous</a></span>
      
      
        <span style="margin-left: 15px"><a href="../ref_params/" style="color: #fcfcfc">Next &raquo;</a></span>
      
    </span>
</div>
    <script>var base_url = '..';</script>
    <script src="../js/theme.js" defer></script>
      <script src="../search/main.js" defer></script>

</body>
</html>
